/*
* filename : sqliteinterface.h
* path: ./
* author: renxiaojun
* date : 2018-07-24
* version : 1.0
* note : invoke the sqlite interface. 
*/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <unistd.h> 
#include "sqliteinterface.h"
#include<stdlib.h>

struct sqlite3 *pdatebase;
char g_SqlBuf[SQLMAXBUF + 1];
/*****************************************************************************
 * Function :  InitConnection
 * Author:      liuhuizhe
 * Date:         2013-04-06
 * Input:        N/A
 * Output:      N/A
 * Return:      DB_ERROR_TYPE
 * other:        N/A
 * Description: init pdatebase
******************************************************************************/
long InitConnection(void)
{
    if (NULL != pdatebase)
    {
        //close db first
        if (DB_OK != CloseConnection(pdatebase))
        {
            return DB_ERROR_FAILURE;
        }
    }
    pdatebase = NULL;
    return DB_OK;
}

/*****************************************************************************
 * Function :  OpenConnection
 * Author:      liuhuizhe
 * Date:         2013-04-06
 * Input:        char *pDBName  db name
 * Output:      N/A
 * Return:      DB_ERROR_TYPE
 * other:        N/A
 * Description: open/create database
******************************************************************************/
long OpenConnection(char *pDBName)
{
    //check param
    if (NULL == pDBName || strlen(pDBName) <= 0)
    {
        return DB_ERROR_INVALID_PARAM;
    }
    if (NULL != pdatebase)
    {
        return DB_OK;
    }
    //invoke sqlite interface to open/create database
    int iResult = sqlite3_open((const char *)pDBName,&pdatebase);
    if(SQLITE_OK != iResult)
    {
      return DB_ERROR_FAILURE;
    }
    printf("open database ok\r\n");
    return DB_OK;
}

/*****************************************************************************
 * Function :  CloseConnection
 * Author:      liuhuizhe
 * Date:         2013-04-06
 * Input:        sqlite3 *pDB  dbhandle
 * Output:      N/A
 * Return:      DB_ERROR_TYPE
 * other:        N/A
 * Description: close database
******************************************************************************/
long CloseConnection(sqlite3 *pDB)
{
    if (NULL == pDB)
    {
        return DB_OK;
    }
    //invoke sqlite interface to close database
    int iResult = sqlite3_close(pDB);
    
    if(SQLITE_OK != iResult)
    {
      return DB_ERROR_FAILURE;
    }
    pdatebase = NULL;
    return DB_OK;
}
/*****************************************************************************
 * Function : ExecSQL
 * Author:      liuhuizhe
 * Date:         2013-04-06
 * Input:        char *pSql
 * Output:      N/A
 * Return:      DB_ERROR_TYPE
 * other:        N/A
 * Description: SQL语句执行
******************************************************************************/
long ExecSQL(char *pSql)
{
    if (NULL == pSql || strlen(pSql) <= 0 || NULL == pdatebase)
    {
        return DB_ERROR_INVALID_PARAM;
    }
    char *pErrMsg ;
    printf("pSql11: %s\r\n",pSql);
    int iResult = sqlite3_exec(pdatebase, pSql, 0, 0, &pErrMsg);
        printf("pSql11: %s\r\n",pSql);
    if(SQLITE_OK != iResult)
    {
      printf("%s\n", pErrMsg);
      sqlite3_free(pErrMsg);
      return DB_ERROR_FAILURE;
    }
    return DB_OK;
}

int TableInsertExecSQL(char *pTable,char *pName,char *pValue)//插入
{
	char pSql[100] = {0};
	//printf("insert\r\n");
	if(pTable==NULL||pName==NULL||pValue==NULL)
	{
		return -1;
	}
	//char *pSql = "insert into RFID (name,data) values ('AAA','BBBBBB')";
	sprintf(pSql,"insert into %s (name,data) values ('%s','%s')",pTable,pName,pValue);
	printf("%s\r\n",pSql);
	
	if(DB_OK == ExecSQL(pSql))
	{
		return 1;
	}
	return -1;
	
}
int TableDeleteExecSQL(char *pTable,char *pName)//删除
{
	if(pTable==NULL||pName==NULL)
	{
		return -1;
	}
	char *pSql=NULL;
	sprintf(pSql,"delete from %s where name = '%s'",pTable,pName);
	if(DB_OK == ExecSQL(pSql))
	{
		return 1;
	}
	return -1;
}
int TableUpdateExecSQL(char *pTable,char *pName,char *new_pLine,char *new_pValue)//修改
{
	char pSql[60] = {0};
	if(pTable==NULL || pName == NULL|| new_pLine == NULL|| new_pValue == NULL )
	{
		return -1;
	}
	sprintf(pSql,"update %s set %s = '%s' where name = '%s'",pTable,new_pLine,new_pValue,pName);
	printf("%s\r\n",pSql);
	if(DB_OK == ExecSQL(pSql))
	{
		return 1;
	}
	return -1;
}

/*****************************************************************************
 * Function : GetTable
 * Author:      renxiaojun
 * Date:         2018-07-24
 * Input:        char *pSql
 *                  char ***pTableResult
 *                  int *pRow
 *                  int *pColumn
 * Output:      N/A
 * Return:      DB_ERROR_TYPE
 * other:        N/A
 * Description: exec select sql
******************************************************************************/
long GetTable(char *pSql, char ***pTableResult, int *pRow, int *pColumn)
{
	int i = 0;
	int j =0;
	int iRow = 0;
	int iColumn = 0;
	char ** ppTable = NULL;
    if (NULL == pSql || strlen(pSql) <= 0 || NULL == pTableResult || NULL == pRow || NULL == pColumn || NULL == pdatebase)
    {
        return DB_ERROR_INVALID_PARAM;
    }
    char* pErrMsg = NULL;
	printf("pSql:%s\r\n",pSql);
	int iResult = sqlite3_get_table(pdatebase, pSql, pTableResult, &iRow, &iColumn,&pErrMsg);
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
        sqlite3_free(pErrMsg);
        return DB_ERROR_FAILURE;
    }
   // *pTableResult = ppTable;
    *pRow = iRow;
    *pColumn = iColumn;
    //strcpy(*ret,(*pTableResult)[2*iColumn+2],strlen((*pTableResult)[2*iColumn+2]));
    
    printf("iRow:%d   iColumn:%d  \r\n",iRow,iColumn);
    for(i=1;i<=iRow;i++)
	{
		for(j=1;j<iColumn;j++)
		{
			//printf("%s\t\t",(*pTableResult[i*(*pColumn)+j]));
			printf("%s\t\t",(*pTableResult)[i*iColumn+j]);
		}
		printf("\n");
	}
	
    sqlite3_free(pErrMsg);
    return DB_OK;
}

//select weight
int selectWeightTable(char *pSelect)
{
	int i = 0;
	int j =0;
	int data = 0;
	int iRow = 0;
	int iColumn = 0;
	char pSql[40] = {0};
	sprintf(pSql,"select * from weight where name = '%s'",pSelect);

	char ** ppTable = NULL;
    if (NULL == pSql || strlen(pSql) <= 0 || NULL == pdatebase)
    {
        return DB_ERROR_INVALID_PARAM;
    }
    char* pErrMsg = NULL;
	printf("pSql:%s\r\n",pSql);
	int iResult = sqlite3_get_table(pdatebase, pSql, &ppTable, &iRow, &iColumn,&pErrMsg);
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    
    if(0 == iRow)
    {
    	return -1;
    }
    
	printf("iRow:%d   iColumn:%d  \r\n",iRow,iColumn);
    for(i=1;i<=iRow;i++)
	{
		for(j=1;j<iColumn;j++)
		{
			printf("%s\t\t",ppTable[i*iColumn+j]);
		}
		printf("\n");
	}
	
	data = atoi(ppTable[1*iColumn+2]);
	printf("data:%d\r\n",data);
    sqlite3_free(pErrMsg);
    return data;
}


//select weight
int showWeightTable(char *pSelect,int weightData[10])
{
	int i = 0;
	int j =0;
	int data = 0;
	int iRow = 0;
	int iColumn = 0;
	char pSql[40] = {0};
	sprintf(pSql,"select * from weight where name = '%s'",pSelect);

	char ** ppTable = NULL;
    if (NULL == pSql || strlen(pSql) <= 0 || NULL == pdatebase)
    {
        return DB_ERROR_INVALID_PARAM;
    }
    char* pErrMsg = NULL;
	printf("pSql:%s\r\n",pSql);
	int iResult = sqlite3_get_table(pdatebase, pSql, &ppTable, &iRow, &iColumn,&pErrMsg);
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    
    if(0 == iRow)
    {
    	return -1;
    }
    
	printf("iRow:%d   iColumn:%d  \r\n",iRow,iColumn);
    for(i=1;i<=iRow;i++)
	{
		for(j=2;j<iColumn;j++)
		{
			printf("%s\t\t",ppTable[i*iColumn+j]);
			weightData[i-1] = atoi(ppTable[i*iColumn+j]);
		}
		printf("\n");
	}
	
	data = atoi(ppTable[1*iColumn+2]);
	printf("data:%d\r\n",data);
    sqlite3_free(pErrMsg);
    return data;
}

//select weight
int weightTableNum(char *pSelect)
{
	int i = 0;
	int j =0;
	int data = 0;
	int iRow = 0;
	int iColumn = 0;
	char pSql[40] = {0};
	sprintf(pSql,"select * from weight where name = '%s'",pSelect);

	char ** ppTable = NULL;
    if (NULL == pSql || strlen(pSql) <= 0 || NULL == pdatebase)
    {
        return DB_ERROR_INVALID_PARAM;
    }
    char* pErrMsg = NULL;
	printf("pSql:%s\r\n",pSql);
	int iResult = sqlite3_get_table(pdatebase, pSql, &ppTable, &iRow, &iColumn,&pErrMsg);
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    
    if(0 == iRow)
    {
    	return -1;
    }
    
	printf("iRow:%d   iColumn:%d  \r\n",iRow,iColumn);
    for(i=1;i<=iRow;i++)
	{
		for(j=1;j<iColumn;j++)
		{
			printf("%s\t\t",ppTable[i*iColumn+j]);
		}
		printf("\n");
	}
    return iRow;
}

//logon 
int selectLogonTable(char *pSelect)//查找登录表(Name)
{
	int i = 0;
	int j =0;
	int data = 0;
	int iRow = 0;
	int iColumn = 0;
	char pSql[40] = {0};
	//sprintf(pSql,"select * from logon where name = '%s'",pSelect);
    sprintf(pSql,"select * from logon where name = '%s'",pSelect);
	char ** ppTable = NULL;
    if (NULL == pSql || strlen(pSql) <= 0 || NULL == pdatebase)
    {
        return DB_ERROR_INVALID_PARAM;
    }
    char* pErrMsg = NULL;
	printf("pSql:%s\r\n",pSql);
	int iResult = sqlite3_get_table(pdatebase, pSql, &ppTable, &iRow, &iColumn,&pErrMsg);
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    
    if(0 == iRow)
    {
    	return -1;
    }
    return 1;
	printf("iRow:%d   iColumn:%d  \r\n",iRow,iColumn);
    for(i=1;i<=iRow;i++)
	{
		for(j=1;j<iColumn;j++)
		{
			printf("%s\t\t",ppTable[i*iColumn+j]);
		}
		printf("\n");
	}
	
	data = atoi(ppTable[1*iColumn+2]);
	printf("data:%d\r\n",data);
    sqlite3_free(pErrMsg);
    return 1;
}
int LogonTable(char *pName,char *pwd)//查找登录表(Name,pwd)
{
	int i = 0;
	int j =0;
	int data = 0;
	int iRow = 0;
	int iColumn = 0;
	char pSql[100] = {0};
	sprintf(pSql,"select * from logon where name = '%s' and data = '%s'",pName,pwd);
	char ** ppTable = NULL;
    if (NULL == pSql || strlen(pSql) <= 0 || NULL == pdatebase)
    {
        return -1;
    }
    char* pErrMsg = NULL;
	printf("pSql:%s\r\n",pSql);
	int iResult = sqlite3_get_table(pdatebase, pSql, &ppTable, &iRow, &iColumn,&pErrMsg);
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    
    if(0 == iRow)
    {
    	return -1;
    }
    printf("iRow:%d   iColumn:%d  \r\n",iRow,iColumn);
	return 1;
    for(i=1;i<=iRow;i++)
	{
		for(j=1;j<iColumn;j++)
		{
			printf("%s\t\t",ppTable[i*iColumn+j]);
		}
		printf("\n");
	}
	data = atoi(ppTable[1*iColumn+2]);
	printf("data:%d\r\n",data);
	return 1;
    sqlite3_free(pErrMsg);
    return 1;
}

//RFID
int RFIDUpdataTable(char *Old,char *New)
{
	if(Old==NULL||New==NULL)
	{
		return -1;
	}
	char pSql[100] = {0};
	sprintf(pSql,"update RFID set data ='%s' where data = '%s'",New,Old);
	if(DB_OK == ExecSQL(pSql))
	{
		return 1;
	}
	return -1;	
}
int selectRFIDTable(char *pSelect)//查找登录表(Name)
{
	int i = 0;
	int j =0;
	int data = 0;
	int iRow = 0;
	int iColumn = 0;
	char pSql[40] = {0};
	//sprintf(pSql,"select * from logon where name = '%s'",pSelect);
    sprintf(pSql,"select * from RFID where data = '%s'",pSelect);
	char ** ppTable = NULL;
    if (NULL == pSql || strlen(pSql) <= 0 || NULL == pdatebase)
    {
        return DB_ERROR_INVALID_PARAM;
    }
    char* pErrMsg = NULL;
	printf("pSql:%s\r\n",pSql);
	int iResult = sqlite3_get_table(pdatebase, pSql, &ppTable, &iRow, &iColumn,&pErrMsg);
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    
    if(0 == iRow)
    {
    	return -1;
    }
    return 1;
	printf("iRow:%d   iColumn:%d  \r\n",iRow,iColumn);
    for(i=1;i<=iRow;i++)
	{
		for(j=1;j<iColumn;j++)
		{
			printf("%s\t\t",ppTable[i*iColumn+j]);
		}
		printf("\n");
	}
	
	data = atoi(ppTable[1*iColumn+2]);
	printf("data:%d\r\n",data);
    sqlite3_free(pErrMsg);
    return 1;
}
int selectRFIDTablePwd(char *pSelect)//查找登录表(pwd)
{
	int i = 0;
	int j =0;
	int data = 0;
	int iRow = 0;
	int iColumn = 0;
	char pSql[100] = {0};
	//sprintf(pSql,"select * from logon where data = '%s'",pSelect);
    sprintf(pSql,"select * from RFID where data = '%s'",pSelect);
	char ** ppTable = NULL;
    if (NULL == pSql || strlen(pSql) <= 0 || NULL == pdatebase)
    {
        return DB_ERROR_INVALID_PARAM;
    }
    char* pErrMsg = NULL;
	printf("pSql:%s\r\n",pSql);
	int iResult = sqlite3_get_table(pdatebase, pSql, &ppTable, &iRow, &iColumn,&pErrMsg);
    if (SQLITE_OK != iResult)
    {
        printf("%s\n", pErrMsg);
        sqlite3_free(pErrMsg);
        return -1;
    }
    
    if(0 == iRow)
    {
    	return -1;
    }
    return 1;
	printf("iRow:%d   iColumn:%d  \r\n",iRow,iColumn);
    for(i=1;i<=iRow;i++)
	{
		for(j=1;j<iColumn;j++)
		{
			printf("%s\t\t",ppTable[i*iColumn+j]);
		}
		printf("\n");
	}
	
	data = atoi(ppTable[1*iColumn+2]);
	printf("data:%d\r\n",data);
    sqlite3_free(pErrMsg);
    return 1;
}
/*****************************************************************************
 * Function : ReleaseTableResult
 * Author:      liuhuizhe
 * Date:         2013-04-06
 * Input:        char ***pTableResult
 * Output:      N/A
 * Return:      DB_ERROR_TYPE
 * other:        N/A
 * Description: release select result
******************************************************************************/

void ReleaseTableResult(char **pTableResult)
{
    if (NULL != pTableResult)
    {
        sqlite3_free_table(pTableResult);
    }
}



